﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace AdoNet
{
    class Operation
    {

        public readonly static string constr = "server=.;user=sa;pwd=123456;database=Login";

        public readonly static SqlConnection myconn = new SqlConnection(constr);

        private static SqlCommand mycomm = null;

       
        public static void Aelaw(string sql) //添加、更新和删除数据
        {

            try
            {
                myconn.Open();

                mycomm = new SqlCommand(sql, myconn);

                var s = mycomm.ExecuteNonQuery();

                Console.WriteLine("受影响的行数为：" + s);//返回受影响的行数

            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {
                mycomm.Clone();
                myconn.Close();
            }

        }


        //读取数据
        //法一：使用SqlDataReader
        public static void DataRead(string sql)
        {
            try
            {
                myconn.Open();
                Console.WriteLine("成功打开数据库");

                mycomm = new SqlCommand(sql, myconn);

                SqlDataReader myread = mycomm.ExecuteReader();



                for (int i = 0; i < myread.FieldCount; i++)
                {
                    Console.Write(myread.GetName(i) + " ");//获取列名
                }

                Console.WriteLine();



                object[] ob = new object[myread.FieldCount];



                while (myread.Read())
                {
                    myread.GetValues(ob);

                    foreach (var item in ob)
                    {
                        Console.Write(item + " ");
                    }
                    Console.WriteLine();
                }

            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {
                mycomm.Clone();
                myconn.Close();
            }
        }



        //法二：使用SqlDataAdapter+DataTable
        public static void Adapter(string sql)
        {


            SqlDataAdapter dataAdapter = null;

            DataTable myTable = new DataTable();

            try
            {
                myconn.Open();

                Console.WriteLine("成功打开数据库");

                mycomm = new SqlCommand(sql, myconn);

                dataAdapter = new SqlDataAdapter(mycomm);

                dataAdapter.Fill(myTable);

            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {
                mycomm.Clone();
                myconn.Close();

            }


            OutValues(myTable);//输出表中的数据

        }

        static void OutValues(DataTable table)
        {

            foreach (DataColumn item in table.Columns)
            {
                Console.Write(item + " ");//获取列名
            }

            Console.WriteLine();

            foreach (DataRow item in table.Rows)
            {
                foreach (DataColumn col in table.Columns)
                {

                    Console.Write(item[col] + " ");

                }
                Console.WriteLine();
            }
        }

    }
}
